/**IMPORT OTHER KITCHEN CATEGORIES DATA FROM AMAZON***/
PROC IMPORT 
DATAFILE ="C:\Amazon Price Dynamics\Data Scraped\Home_Kitchen\merged\kitchenall_amazon_4_7_20.csv"
OUT= amazon_kitchnequip3 DBMS=csv
RfrigidLACE;
run;

PROC IMPORT 
DATAFILE ="C:\Amazon Price Dynamics\Data Scraped\Amazon Others_merged\luggage_boardgamesdata_4_6_2020.csv"
OUT= amazon_luggageboardgames DBMS=csv
RfrigidLACE;
run;
proc contents data = amazon_kitchnequip3;
run;
/**selecting only deep fryer and microwave; water pitcher data not available in the external sites and electric cooker is the focal category**/
data amazon_kitchnequip4 (keep = time1 ASIN seller_ID brand categ Answered_Questions price1 Buy_Box_Price1 Seller_Rating
num_seller_Posrating  num_seller_ratings Product_Star_Rate Sales_Rank_Own_Categ Sales_Rank_Sub_categ 
Product_reviews condition Freeship  FBA Prime Instock season);
set amazon_kitchnequip3;
where categ in ('Deep Fryer', 'Microwave');
if Buy_Box_Price1 = . then Nobuyboxwinner = 1;
else Nobuyboxwinner = 0;
run;
PROC SORT DATA = amazon_kitchnequip4 NODUPKEY OUT= amazon_kitchnequip4;
BY TIME1 ASIN Seller_ID condition ;
RUN;


data amazon_luggageboardgames1 (keep = time1 ASIN seller_ID brand categ Answered_Questions price1 Buy_Box_Price1 Seller_Rating
num_seller_Posrating  num_seller_ratings Product_Star_Rate Sales_Rank_Own_Categ Sales_Rank_Sub_categ 
Product_reviews condition Freeship  FBA Prime Instock season);
set amazon_luggageboardgames;
if Answered_Questions  = . then Answered_Questions  = 0;
run;
/*PROC SORT DATA = amazon_luggageboardgames1 NODUPKEY OUT= amazon_luggageboardgames2;*/
/*BY TIME1 ASIN Seller_ID condition ;*/ /*already deduped in data processing*/
/*RUN;*/
/*stacking data sets*/
data amazon_allothers;
set amazon_kitchnequip4 amazon_luggageboardgames1;
run;
proc freq data = amazon_allothers;
tables categ;
run;

/***3P SELLER CHARACTERISTICS***/

/****RESTRICTING DATA TO THE TIME LENGTH EXTERNAL SITE INFORMATION IS AVAILABLE***/

proc freq data = amazon_allothers1;
tables brand;
run;
data amazon_allothers1;
SET amazon_allothers;
where time1 >= '14Dec2017'd and time1 <= '06May2018'd ;
/*length brand1 $50.;*/
/*brand1 = brand;*/
RUN;

/****price change by ASIN***/

/**COMPUTING PRICE CHANGE BY ID, BRAND AND CATEG***/

proc sort data = amazon_allothers1 NODUPkey out=amazon_allothers2 ;
by  ASIN seller_ID time1 ;
run;
proc expand data=amazon_allothers2 out=amazon_allothers3 method = none; 
  by ASIN seller_ID ;
  convert price1 = price_lag1     / transformout=(lag 1); 
RUN;

proc sql;
create table price_changes_amazonothers as
select 
time1,
ASIN,
categ,
brand,
seller_ID,
(case when price_lag1 ne . and price_lag1 ne 0 then (price1-price_lag1)/price_lag1 else 0 end) as pricechange

from
amazon_allothers3
group by
time1,
ASIN,
categ,
brand,
seller_ID;
quit;
/***count number of price changes by seller type and ASIN***/
proc sql;
create table numpricechange_amazonothers  as
select
ASIN,
categ,
brand,
seller_ID,
count(distinct time1) as num_days,
sum(case when pricechange ne 0 then 1 else 0 end) as num_pricechange
from
price_changes_amazonothers
group by
ASIN,
categ,
brand,
seller_ID;
quit;
proc sort data = numpricechange_amazonothers;
by categ;
run;
proc univariate data = numpricechange_amazonothers;
var num_days;
by categ;
/*where categ = 'Microwave';*/
run;

proc freq data =numpricechange_amazonothers;
tables categ;
run;

/**selecting 3P sellers who have been on the marketplace > 10 days**/
DATA REDUCED_SELLERS;
SET numpricechange_amazonothers;
WHERE num_days > 10;
RUN;


proc sort data = REDUCED_SELLERS (keep = ASIN seller_ID) nodupkey out = reduced_seller_list;
by ASIN seller_ID;
run;
proc sort data = amazon_allothers1 out = amazon_allothers_sort;
by ASIN seller_ID;
run;
data amazon_allothers_finalsellers;
merge amazon_allothers_sort (in=a) reduced_seller_list (in=b);
by ASIN seller_ID;
if a and b;
run;

/**classifying used and new product sellers**/

data amazon_allothers_usednew;
set amazon_allothers_finalsellers;
length seller_type $50.;
IF CONDITION = 0 AND SELLER_ID = 'Amazon'  THEN SELLER_TYPE = 'Amazon Old';
ELSE IF CONDITION = 1 AND SELLER_ID = 'Amazon' THEN SELLER_TYPE = 'Amazon New';
ELSE IF CONDITION = 0 AND SELLER_ID NE 'Amazon' THEN SELLER_TYPE = '3P Old';
ELSE IF CONDITION = 1 AND SELLER_ID NE 'Amazon' THEN SELLER_TYPE = '3P New';
run;
proc freq data = amazon_allothers_usednew;
tables condition seller_type;
run;

proc print data = amazon_allothers_usednew (obs=10);
var seller_id;
where seller_type in ( '3P Old','3P New');
run;


/***DETERMINING SELLER CHARACTERISTICS FOR EACH SELLER***/

data amazon_allothers_new;
set amazon_allothers_usednew;
where seller_type = '3P New';
run;
proc freq data = amazon_allothers_usednew;
tables FBA*seller_type /list;
where categ = 'Luggage';
run;

proc sql;
create table SELLER_VARS as
select 
categ,
Seller_ID,
SELLER_TYPE,
count(ASIN) as num_ASIN_sold,
count(distinct ASIN) as num_uniq_ASIN_sold,
mean(condition) as mean_condition,
mean(freeship) as mean_freeship,
mean(FBA) as mean_FBA,
mean(Prime) as mean_prime,
MEAN(seller_rating) as MEAN_SELLER_RATE,
min(seller_rating) as Min_SELLER_RATE,
max(seller_rating) as Max_SELLER_RATE,
MAX(num_seller_Posrating) AS MAX_PERC_POSRATING,
Min(num_seller_Posrating) AS Min_PERC_POSRATING,
Mean(num_seller_Posrating) AS Mean_PERC_POSRATING,
MAX(num_seller_ratings) AS MAX_NUM_RATINGS,
Min(num_seller_ratings) AS Min_NUM_RATINGS,
Mean(num_seller_ratings) AS Mean_NUM_RATINGS,
MEAN(price1) as MEAN_PRICE,
Max(price1) as Max_PRICE,
Min(price1) as Min_PRICE
from 
amazon_allothers_new
group by
categ,
Seller_ID,
SELLER_TYPE
;
quit;

PROC EXPORT 
DATA=SELLER_VARS
DBMS=csv
OUTFILE= "C:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\3psellercharac_4_15_20.csv"
RfrigidLACE;
run;
proc contents data = SELLER_VARS;
run;
proc means data = SELLER_VARS n mean std nway;
class categ;
var 
MEAN_SELLER_RATE
MAX_NUM_RATINGS
MAX_PERC_POSRATING
mean_FBA
mean_freeship
num_uniq_ASIN_sold;
output out = summ_sellercharc ;
run;

proc standard data = SELLER_VARS  mean=0 std=1 out= SELLER_standvars_othercateg;
VAR 
MAX_NUM_RATINGS
MAX_PERC_POSRATING
MEAN_PRICE
MEAN_SELLER_RATE
Max_PRICE
Max_SELLER_RATE
Mean_NUM_RATINGS
Mean_PERC_POSRATING
Min_NUM_RATINGS
Min_PERC_POSRATING
Min_PRICE
Min_SELLER_RATE
mean_FBA
mean_prime
mean_condition
mean_freeship
num_ASIN_sold
num_uniq_ASIN_sold
;
by categ;
RUN;
/*PROC PRINT DATA = SELLER_standvars_Cooker (obs=20);*/
/*RUN;*/

/**RUNNING VARCLUS PROCEDURE TO REMOVE CORRELATED VARIABLES**/


/**RUNNING FASTCLUS PROCEDURE for each category***/
proc freq data = SELLER_standvars_othercateg;
tables categ;
run;
data luggage_clusteranalysis;
set SELLER_standvars_othercateg;
where categ = 'Luggage';
run;
proc varclus data=luggage_clusteranalysis    outtree=tree centroid MAXITER = 1000 maxclusters= 5  CENTROID;
var 
Mean_NUM_RATINGS
Mean_PERC_POSRATING
mean_FBA
mean_prime
mean_freeship
num_ASIN_sold
num_uniq_ASIN_sold
;
run;

proc fastclus data = luggage_clusteranalysis
 outseed= mean1
 maxc= 10 converge = 0 maxiter=100000 summary;
 var 
Max_NUM_RATINGS
Mean_PERC_POSRATING
mean_FBA
mean_prime
mean_freeship
num_ASIN_sold
num_uniq_ASIN_sold
;
run;
data seed;
set mean1;
if _freq_>5;
run;
proc fastclus data=luggage_clusteranalysis seed=seed maxc=10 least=1 out=sellers_clusters_luggage;
 var 
/*Max_NUM_RATINGS*/
Mean_PERC_POSRATING
mean_FBA
mean_prime
mean_freeship
num_ASIN_sold
/*num_uniq_ASIN_sold*/
;
run;

PROC EXPORT 
DATA=sellers_clusters_luggage
DBMS=csv
OUTFILE= "C:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\clusteroutput_luggage_5_5_20.csv"
REPLACE;
run;

/*PROC FREQ DATA = sellers_clusters_luggage;*/
/*TABLES CLUSTER;*/
/*RUN;*/

/***MERGING CLUSTERS BACK TO THE UN-STANDARDIZED DATA SET TO RECOVER MEAN CLUSTER CHARACTERISTICS****/

PROC SORT DATA = sellers_clusters_luggage (KEEP= Seller_ID Cluster ) OUT=sellers_clusters_luggage_SORT;
BY Seller_ID;
RUN;
DATA SELLER_VARS_luggage;
SET SELLER_VARS;
WHERE CATEG = 'Luggage';
RUN;

PROC SORT DATA = SELLER_VARS_luggage out=SELLER_VARS_luggage_sort;
BY Seller_ID;
RUN;

data seller_luggage_clusters (drop= cluster);
merge sellers_clusters_luggage_SORT (in=a) SELLER_VARS_luggage_sort (in=b);
BY Seller_ID;
if a and b;
IF CLUSTER = 1 THEN seller_type = 'Cluster 1';
if CLUSTER = 2 THEN seller_type = 'Cluster 2';

run;
PROC CONTENTS DATA = numpricechange_amazonothers;
RUN;
proc means data = seller_luggage_clusters n mean min max std nway ;
class seller_type;
var
MAX_NUM_RATINGS
MAX_PERC_POSRATING
MEAN_SELLER_RATE
Max_SELLER_RATE
Mean_NUM_RATINGS
Mean_PERC_POSRATING
Min_NUM_RATINGS
Min_PERC_POSRATING
Min_PRICE
Min_SELLER_RATE
mean_FBA
mean_prime
mean_condition
mean_freeship
num_ASIN_sold
num_uniq_ASIN_sold;
output out= seller_luggage_cluster_charac ;
run;

PROC EXPORT 
DATA=seller_luggage_cluster_charac
DBMS=csv
OUTFILE= "C:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\clusterPROFILEs_luggage_5_5.csv"
REPLACE;
run;
/***MERGING CLUSTER OUTPUT WITH THE NUMBER OF PRICE CHANGES AND NUMBER OF DAYS TREND***/
data numpricechange_luggage;
set numpricechange_amazonothers;
where categ = 'Luggage';
run;

PROC SORT DATA = numpricechange_luggage OUT= SORTED_PRICECHANGES;
BY SELLER_ID;
RUN;
PROC SORT DATA = seller_luggage_clusters out= sorted_sellerclusters;
by seller_id;
run;
DATA SELLER_PRICECHANGE_luggage;
MERGE SORTED_PRICECHANGES (IN=A) sorted_sellerclusters (IN=B);
BY SELLER_ID;
IF A AND B;
RUN;
/***above file exported as PRICECHANGES_SELLERCLUSTER_luggage_4_24.csv****/

/******MERGING CLUSTER OUTPUT WITH THE DAILY PRICE TREND DATA AT ASIN LEVEL***/
/**/
/*proc freq data = amazon_allothers_usednew;*/
/*tables seller_type;*/
/*run;*/
/*proc contents data = amazon_allothers_usednew;*/
/*run;*/
data luggage_usednew;
set amazon_allothers_usednew;
where categ = 'Luggage';
run;

proc sql;
create table price_dev_BB as
select 
time1,
ASIN,
Seller_ID,
(price1-Buy_Box_Price1)as dev_BBprice

from 
luggage_usednew
group by
time1,
ASIN,
Seller_ID
;
quit;
/***ADDED VARIABLES AFTER CONVO WITH BRYAN --- NEED TO INCLUDE THESE IN THE SELLER ENTRY AND BUY BOX MODELS***/
/**SELLER ENTRY: NON BUY BOX 3P PRICE; BUY BOX MODEL: DIFFERENCE FROM LOWEST PRICE OFFER***/
proc sql;
   create table lowestprice_offer as
   select 
      time1,
      ASIN,
/*      Buy_Box_Price1,*/
      min(price1) as lowestprice_offer
   from luggage_usednew
   group by time1, ASIN;
quit;
proc print data = lowestprice_offer (obs=20);
run;

data non_buybox3pprice;
set luggage_usednew;
where seller_id ne 'Amazon' and price1 ne Buy_Box_Price1;
run;
proc sql;
   create table mean_nonbuybox3pprice as
   select 
      time1,
      ASIN,
      mean(price1) as nonbuybox3p_price
   from non_buybox3pprice
   group by time1, ASIN;
quit;

/***adding the mean non buy box price for the seller entry model*****/
proc sort data = mean_nonbuybox3pprice;
by time1 ASIN;
run;

proc sort data = luggage_usednew out = focal_rival_prods26_sort;
by time1 ASIN;
run;

data focal_rival_prods27;
merge focal_rival_prods26_sort (in=a) mean_nonbuybox3pprice (in=b) ;
by time1 ASIN;
if a and b ;
run;
proc freq data = focal_rival_prods27;
tables asin*brand / list;
run;
proc means data = focal_rival_prods27 n sum nway noprint;
class time1 ASIN seller_id seller_type;
var price1 Buy_Box_Price1 nonbuybox3p_price Seller_Rating num_seller_Posrating  num_seller_ratings condition Freeship  FBA Prime Instock ;
output out = price_trend sum= ;
run;
PROC SORT DATA = price_trend OUT= price_trend1;
BY SELLER_ID ;
RUN;
data price_trend2 (drop=seller_type);
set price_trend1;
where seller_type = '3P New';
run;
data price_trend3;
set price_trend1;
where seller_type ne '3P New';
run;

PROC SORT DATA = seller_luggage_clusters (KEEP= SELLER_ID seller_type) OUT= SELLER_TYPE1;
BY SELLER_ID ;
RUN;

DATA newSELLER_TYPE_TREND ;
MERGE SELLER_TYPE1 (IN=A) price_trend2 (IN=B);
BY SELLER_ID ;
IF B;
RUN;
/*proc print data = newSELLER_TYPE_TREND (obs=20);*/
/*run; */
data SELLER_TYPE_TREND;
set newSELLER_TYPE_TREND price_trend3;
run;

PROC SORT DATA = SELLER_TYPE_TREND;
BY TIME1 ASIN;
RUN;

DATA SELLER_TYPE_TREND1;
MERGE SELLER_TYPE_TREND (IN=A) lowestprice_offer (IN=B);
BY TIME1 ASIN;
IF A ;
RUN;
proc print data = SELLER_TYPE_TREND1 (obs=20);
run;
/****EXTRA SUMMARY NEEDED FOR DEVIATION FROM BUY BOX PRICE - THIS VARIABLE IS NOT INCLUDED IN THE FINALDATA USED FOR MODELING**/
proc sort data = price_dev_BB;
by time1 asin seller_id;
run;
proc sort data = SELLER_TYPE_TREND1;
by time1 asin seller_id;
run;

data devbuybox_lugg;
merge seller_type_trend1 (in=a) price_dev_bb (in=b);
by time1 asin seller_id;
if a and b;
run;
proc print data = devbuybox_lugg (obs=10);
/*var nonbuybox3p_price;*/
/*where ASIN = 'B00NQ7QFGM';*/
run;
PROC MEANS DATA = devbuybox_lugg N SUM MEAN MAX MIN NWAY;
CLASS SELLER_TYPE;
VAR dev_BBprice;
OUTPUT OUT = DEV_SUMMARY;
RUN;

/*****************************************/
/*proc freq data = SELLER_TYPE_TREND;*/
/*tables seller_type;*/
/*run;*/

/*PROC EXPORT */
/*DATA=SELLER_TYPE_TREND*/
/*DBMS=csv*/
/*OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\Sellerclusters_luggage_May2020.csv"*/
/*REPLACE;*/
/*run;*/
PROC EXPORT 
DATA=SELLER_TYPE_TREND1
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\Sellerclusters_luggage_July2025.csv"
REPLACE;
run;

/**COMPUTING PRICE CHANGE BY ASIN AND SELLER TYPE***/

proc sort data = SELLER_TYPE_TREND1 NODUPREC out=SELLER_TYPE_TREND11 ;
by  ASIN seller_type seller_ID TIME1 ;
run;
proc expand data=SELLER_TYPE_TREND11 out=SELLER_TYPE_TREND12 method = none; 
  by ASIN seller_type seller_ID;
  convert price1 = price_lag1     / transformout=(lag 1); 
RUN;

proc sql;
create table price_changes_sellertype as
select 
time1,
ASIN,
seller_type,
seller_ID,
price1,
(price1 - lowestprice_offer) as pricediff_fromlowest,
(case when price_lag1 ne . and price_lag1 ne 0 then (price1-price_lag1)/price_lag1 else 0 end) as pricechange
from
SELLER_TYPE_TREND12
group by
time1,
ASIN,
seller_type,
seller_ID;
quit;
proc print data = price_changes_sellertype (obs=20);
run;
/*PROC EXPORT */
/*DATA=price_changes_sellertype*/
/*DBMS=csv*/
/*OUTFILE= "C:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\price_changes_luggage_May2020.csv"*/
/*REPLACE;*/
/*run;*/
PROC EXPORT 
DATA=price_changes_sellertype
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\price_changes_luggage_July2025.csv"
REPLACE;
run;


/***count number of price changes by seller type and ASIN***/

proc sql;
create table num_pricechange_sellertype_asin as
select
seller_ID, 
seller_type,
ASIN,
count(distinct time1) as num_days,
sum(case when pricechange ne 0 then 1 else 0 end) as num_pricechange
from
price_changes_sellertype
group by
seller_ID, 
seller_type,
ASIN;
quit;
/*PROC EXPORT */
/*DATA=num_pricechange_sellertype_asin*/
/*DBMS=csv*/
/*OUTFILE= "C:\Users\F00456N\Documents\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\num_pricechange_sellertype_asin_luggage_May2020.csv"*/
/*REPLACE;*/
/*run;*/
PROC EXPORT 
DATA=num_pricechange_sellertype_asin
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\num_pricechange_sellertype_asin_luggage_May2020.csv"
REPLACE;
run;

/***selecting the representative seller and ASIN per cluster with most number of price changes in the period***/

proc sort data = num_pricechange_sellertype_asin out= sorted_bymostchanges ;
by seller_type descending num_pricechange  DESCENDING NUM_DAYS ASIN;
run;
/**/
/*PROC EXPORT */
/*DATA=sorted_bymostchanges*/
/*DBMS=csv*/
/*OUTFILE= "C:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\sorted_bymostchanges_luggage_May2020.csv"*/
/*REPLACE;*/
/*run;*/
PROC EXPORT 
DATA=sorted_bymostchanges
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\sorted_bymostchanges_luggage_July2025.csv"
REPLACE;
run;
data cluster1seller;
set sorted_bymostchanges;
where seller_type = 'Cluster 1' and ASIN = 'B009R1FEQ8' ;
run;
proc sort data = cluster1seller nodupkey out= cluster1_pricechange_vector;
by seller_type ;
run;
data cluster2sellers;
set sorted_bymostchanges;
where seller_type = 'Cluster 2' and ASIN in ( 'B001DWK4NM' , 'B00144MBWQ', 'B004HKSENY') ;
run;
proc sort data = cluster2sellers nodupkey out= cluster2_pricechange_vector;
by seller_type ASIN;
run;


data amazon;
set sorted_bymostchanges;
where seller_type in ('Amazon New') and ASIN IN ( 'B009R1FEQ8' , 'B00144MBWQ', 'B001DWK4NM','B004HKSENY');
run;


data newandamazonsellers;
set cluster1_pricechange_vector cluster2_pricechange_vector amazon;
run;
proc print data = newandamazonsellers;
run;

/**merging back to the price changes main file to get the trend for the chosen seller IDs and ASINs**/
/*Amazon New	B009R1FEQ8	Samsonite*/
/*Amazon New	B00144MBWQ	Olympia*/
/*Amazon New    B001DWK4NM	Travelers Choice*/
/*Amazon New	B004HKSENY	Rockland*/
/*Cluster 1	B009R1FEQ8	Samsonite*/
/*Cluster 2	B001DWK4NM	Travelers Choice*/
/*Cluster 2	B00144MBWQ	Olympia*/
/*Cluster 2	B004HKSENY	Rockland*/

proc print data = price_changes_sellertype (obs=20);
run;
proc sort data = price_changes_sellertype out= price_changes_sellertype1;
by seller_type seller_ID ASIN;
run;
proc sort data = newandamazonsellers out= newandamazonsellers1;
by seller_type seller_ID ASIN;
run;
data pricechangetrend_outcome;
merge newandamazonsellers1 (in=a) price_changes_sellertype1 (in=b);
by seller_type seller_ID ASIN;
if a and b;
run;
proc sort data = pricechangetrend_outcome (keep=time1) nodupkey out= final_outputvector;
by time1;
run;
%macro pricechange(a,b,c);
data pricechange&b. (keep = time1 pricechange_&b. price&b. pricediff_lowst_&b. );
set pricechangetrend_outcome;
where seller_type = &a. and ASIN = &c.;
rename pricechange = pricechange_&b. ;
rename price1 = price&b.;
rename pricediff_fromlowest = pricediff_lowst_&b.;
run;

data final_outputvector ;
merge final_outputvector (in=a) pricechange&b. (in=b);
by time1;
if a ;
if a and not b then pricechange_&b. = 0;
run;
%mend;
%pricechange(a='Amazon New', b = Amzn_smsnite, c = 'B009R1FEQ8'); 
%pricechange(a='Amazon New', b = Amzn_olympia, c = 'B00144MBWQ'); 
%pricechange(a='Amazon New', b = Amzn_tc, c = 'B001DWK4NM'); 
%pricechange(a='Amazon New', b = Amzn_rcklnd, c = 'B004HKSENY'); 
%pricechange(a='Cluster 1', b = clust1_smsnite, c = 'B009R1FEQ8'); 
%pricechange(a='Cluster 2', b = clust2_tc, c = 'B001DWK4NM'); 
%pricechange(a='Cluster 2', b = clust2_olympia, c = 'B00144MBWQ'); 
%pricechange(a='Cluster 2', b = clust2_rcklnd, c = 'B004HKSENY'); 


/*PROC EXPORT */
/*DATA=final_outputvector*/
/*DBMS=csv*/
/*OUTFILE= "C:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\pricechangetrend_luggage_May2020.csv"*/
/*REPLACE;*/
/*run;*/

PROC EXPORT 
DATA=final_outputvector
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\pricechangetrend_luggage_July2025.csv"
REPLACE;
run;

/***CLUSTER MEMBERSHIP: NUMBER OF SELLERS IN A CLUSTER AT TIME T, BRANDS SOLD BY SELLER TYPES AT TIME T,
NUMBER OF PRICE CHANGES BY OWN AND CROSS BRANDS BY SELLER TYPES AT TIME (T-1), (T-2), (T-3), *******/
/**OUTCOME VECTOR*/
/*Amazon New	B009R1FEQ8	Samsonite*/
/*Amazon New	B073ZG6HXV	Delsey*/
/*Cluster 1	B009R1FEQ8	Samsonite*/
/*Cluster 2	B001DWK4NM	Travelers Choice*/
/*Cluster 2	B00144MBWQ	Olympia*/
/*Cluster 2	B004HKSENY	Rockland*/

/***full list of ASINs and brand**/
/*B00144MBWQ	Olympia*/
/*B001DWK4NM	Travelers Choice*/
/*B004HKSENY	Rockland*/
/*B009R1FEQ8	Samsonite*/
/*B01ELN68IO	Delsey*/
/*B01LAS65T0	Reaction Kenneth Cole*/
/*B073ZG6HXV	Delsey*/


PROC SQL;
CREATE TABLE SELLER_offercharcs AS
SELECT
TIME1,
SELLER_TYPE,
COUNT(DISTINCT SELLER_ID) AS NUM_SELLERS,
COUNT(DISTINCT ASIN) AS NUM_SKUS,
sum(case when ASIN in ('B01ELN68IO','B073ZG6HXV') then 1 else 0 end) AS NUM_delsy,
sum(case when ASIN = 'B00144MBWQ' then 1 else 0 end) as num_olympia, 
sum(case when ASIN = 'B001DWK4NM' then 1 else 0 end) as num_tc,
sum(case when ASIN = 'B004HKSENY' then 1 else 0 end) as num_rcklnd,
sum(case when ASIN = 'B009R1FEQ8' then 1 else 0 end) as num_smsnite,
sum(case when ASIN = 'B01LAS65T0' then 1 else 0 end) as num_kcole
from 
SELLER_TYPE_TREND12
group by
time1,
seller_type;
quit;

proc sort data = SELLER_offercharcs (keep = time1) nodupkey out= SELLER_offercharcs_base;
by time1;
run;

%macro seller_offercharacs(a,b);
data seller_offer&a.;
set SELLER_offercharcs;
where seller_type = &b.;
rename NUM_SELLERS = NUM_SELLERS&a.;
rename NUM_SKUS = NUM_SKUS&a.;
rename NUM_delsy = NUM_delsy&a.;
rename num_olympia = num_olympia&a.;
rename num_tc = num_tc&a.;
rename num_rcklnd = num_rcklnd&a.;
rename num_smsnite = num_smsnite&a.;
rename num_kcole = num_kcole&a.;
run;

proc sort data = seller_offer&a. (drop=  seller_type) out= sorted_seller_type;
by time1;
run;

data SELLER_offercharcs_base;
merge SELLER_offercharcs_base (in=a) sorted_seller_type (in=b);
by time1;
if a ;
if a and not b then do;
NUM_SELLERS&a. = 0;
NUM_SKUS&a. = 0;
NUM_delsy&a. = 0;
num_olympia&a.= 0;
num_tc&a.= 0;
num_rcklnd&a.= 0;
num_smsnite&a.= 0;
num_kcole&a.= 0;
end;
run;
%mend;
%seller_offercharacs(a = used3p, b = '3P Old');
%seller_offercharacs(a = usedamzn, b = 'Amazon Old'); 
%seller_offercharacs(a = amzn, b = 'Amazon New');
%seller_offercharacs(a = clust1, b = 'Cluster 1'); 
%seller_offercharacs(a = clust2, b = 'Cluster 2'); 


/*****COMPETITOR PRICING INFORMATION*****/
/**ACCOUNTING FOR PRICE CHANGES IN EACH SELLER TYPE BY BRANDS****/

proc sql;
create table num_pricechange_sellertype_asin as
select
TIME1,
seller_type,
ASIN,
sum(case when pricechange ne 0 then 1 else 0 end) as num_pricechange
from
price_changes_sellertype
group by
time1,
seller_type,
ASIN;
quit;


proc sort data = num_pricechange_sellertype_asin (keep = time1)nodupkey out= numpricechange_sellerbrandtrend;
by time1;
run;
/**OUTCOME VECTOR*/
/*Amazon New	B009R1FEQ8	Samsonite*/
/*Amazon New	B00144MBWQ	Olympia*/
/*Cluster 1	B009R1FEQ8	Samsonite*/
/*Cluster 2	B001DWK4NM	Travelers Choice*/
/*Cluster 2	B00144MBWQ	Olympia*/
/*Cluster 2	B004HKSENY	Rockland*/

/***full list of ASINs and brand**/
/*B00144MBWQ	Olympia*/
/*B001DWK4NM	Travelers Choice*/
/*B004HKSENY	Rockland*/
/*B009R1FEQ8	Samsonite*/
/*B01ELN68IO	Delsey*/
/*B01LAS65T0	Reaction Kenneth Cole*/
/*B073ZG6HXV	Delsey*/

%macro seller_brand(a,b,c,d);
data num_pricechange_&d.&b.;
set num_pricechange_sellertype_asin;
where ASIN in &c. and seller_type = &a.;
rename num_pricechange = num_pricechange&b.&d.;
run;

proc sql;
create table num_pricechange1_&d.&b. as
select 
time1,
sum(num_pricechange&b.&d.) as num_pricechange1&b.&d.
from 
num_pricechange_&d.&b.
group by
time1;
quit;

proc sort data = num_pricechange1_&d.&b. NODUPKEY out= sorted;
by time1;
run;

data numpricechange_sellerbrandtrend;
merge numpricechange_sellerbrandtrend (in=a) sorted (in=b);
by time1;
if a ;
if a and not b then num_pricechange1&b.&d. = 0;
run;
%mend;

%seller_brand(a='3P Old',b= delsy, c =('B01ELN68IO','B073ZG6HXV'), d= used3p);
%seller_brand(a='Amazon Old',b= delsy, c=('B01ELN68IO','B073ZG6HXV'), d= usedamzn);
%seller_brand(a='Amazon New',b= delsy, c=('B01ELN68IO','B073ZG6HXV'), d= amzn);
%seller_brand(a='Cluster 1',b= delsy, c=('B01ELN68IO','B073ZG6HXV'), d= clust1);
%seller_brand(a='Cluster 2',b= delsy, c=('B01ELN68IO','B073ZG6HXV'), d= clust2);

%seller_brand(a='3P Old',b= smsnite, c =('B009R1FEQ8'), d= used3p);
%seller_brand(a='Amazon Old',b= smsnite, c=('B009R1FEQ8'), d= usedamzn);
%seller_brand(a='Amazon New',b= smsnite, c=('B009R1FEQ8'), d= amzn);
%seller_brand(a='Cluster 1',b= smsnite, c=('B009R1FEQ8'), d= clust1);
%seller_brand(a='Cluster 2',b= smsnite, c=('B009R1FEQ8'), d= clust2);

%seller_brand(a='3P Old',b= tc, c =('B001DWK4NM'), d= used3p);
%seller_brand(a='Amazon Old',b= tc, c=('B001DWK4NM'), d= usedamzn);
%seller_brand(a='Amazon New',b= tc, c=('B001DWK4NM'), d= amzn);
%seller_brand(a='Cluster 1',b= tc, c=('B001DWK4NM'), d= clust1);
%seller_brand(a='Cluster 2',b= tc, c=('B001DWK4NM'), d= clust2);

%seller_brand(a='3P Old',b= olympia, c =('B00144MBWQ'), d= used3p);
%seller_brand(a='Amazon Old',b= olympia, c=('B00144MBWQ'), d= usedamzn);
%seller_brand(a='Amazon New',b= olympia, c=('B00144MBWQ'), d= amzn);
%seller_brand(a='Cluster 1',b= olympia, c=('B00144MBWQ'), d= clust1);
%seller_brand(a='Cluster 2',b= olympia, c=('B00144MBWQ'), d= clust2);

%seller_brand(a='3P Old',b= rcklnd, c =('B004HKSENY'), d= used3p);
%seller_brand(a='Amazon Old',b= rcklnd, c=('B004HKSENY'), d= usedamzn);
%seller_brand(a='Amazon New',b= rcklnd, c=('B004HKSENY'), d= amzn);
%seller_brand(a='Cluster 1',b= rcklnd, c=('B004HKSENY'), d= clust1);
%seller_brand(a='Cluster 2',b= rcklnd, c=('B004HKSENY'), d= clust2);

%seller_brand(a='3P Old',b= kcole, c =('B01LAS65T0'), d= used3p);
%seller_brand(a='Amazon Old',b= kcole, c=('B01LAS65T0'), d= usedamzn);
%seller_brand(a='Amazon New',b= kcole, c=('B01LAS65T0'), d= amzn);
%seller_brand(a='Cluster 1',b= kcole, c=('B01LAS65T0'), d= clust1);
%seller_brand(a='Cluster 2',b= kcole, c=('B01LAS65T0'), d= clust2);


/****************************/
/*****SELLER ATTRIBUTES******/
/****************************/
/***EXTRACTING NUM OF UNIQUE ASINS AND NUM OF ASINS SOLD CONDITION FOR EACH SELLER***/
PROC SQL;
create table seller_asin as
select
time1,
seller_ID,
count(distinct ASIN) as unique_ASIN_sold,
count(distinct time1) as sell_freq
from
SELLER_TYPE_TREND12
group by
time1,
seller_ID
;
quit;
/**MERGING BACK TO THE MAIN DATA SET**/
PROC SORT DATA = SELLER_TYPE_TREND12 OUT= SELLER_TYPE_TREND4;
BY time1 seller_ID;
RUN;
PROC SORT DATA = seller_asin OUT= seller_asin1;
BY time1 seller_ID;
RUN;
DATA SELLER_TYPE_TREND5;
MERGE SELLER_TYPE_TREND4 (IN=A) seller_asin1 (IN=B);
BY time1 seller_ID;
IF A AND B ;
RUN;

/**creating cumulative sell freq over time***/
proc sort data = SELLER_TYPE_TREND5 (keep= time1 seller_ID sell_freq) nodupkey out=SELLER_TYPE_TREND6;
by seller_ID time1;
run;

data cum_sellfreq;
  set SELLER_TYPE_TREND6;
  by seller_ID ;
  if first.seller_ID then cum_sellerfreq = 0;
  cum_sellerfreq +sell_freq;
run;
/**merging back to main data set**/
PROC SORT DATA = SELLER_TYPE_TREND5 OUT= SELLER_TYPE_TREND7;
BY time1 seller_ID;
RUN;
PROC SORT DATA = cum_sellfreq OUT= cum_sellfreq1;
BY time1 seller_ID;
RUN;
DATA SELLER_TYPE_TREND8;
MERGE SELLER_TYPE_TREND7 (IN=A) cum_sellfreq1 (IN=B);
BY time1 seller_ID;
IF A AND B ;
RUN;

PROC SQL;
create table SELLER_CHAR as
select
time1,
seller_type,
SUM(FBA) AS NUM_FBA,
sum(prime) as num_prime,
SUM(FREESHIP) AS NUM_FREESHIP,
SUM(CONDITION) AS NUM_NEWOFFERS,
sum(case when condition = 0 then 1 else 0 end) AS NUM_USEDOFFERS,
max(num_seller_Posrating) as max_perct_sellerrating,
mean(num_seller_Posrating) as mean_perct_sellerrating,
min(num_seller_Posrating) as min_perct_sellerrating,
max(num_seller_ratings) as max_num_seller_rating,
mean(num_seller_ratings) as mean_num_seller_rating,
min(num_seller_ratings) as min_num_seller_rating,
max(seller_rating) as max_seller_star_rating,
mean(seller_rating) as mean_seller_star_rating,
min(seller_rating) as min_seller_star_rating,
max(unique_ASIN_sold) as max_uniq_ASIN,
mean(unique_ASIN_sold) as mean_uniq_ASIN,
min(unique_ASIN_sold) as min_uniq_ASIN,
max(cum_sellerfreq) as max_cumsalefreq,
mean(cum_sellerfreq) as mean_cumsalefreq,
min(cum_sellerfreq) as min_cumsalefreq
from
SELLER_TYPE_TREND8
group by
time1,
seller_type
;
quit;

/**rename each seller char to individual columns for each seller cluster for model build, i.e., max_fba_clust1, min_fba_clust1 etc. ****/

proc sort data = SELLER_CHAR  (keep=time1) nodupkey out=seller_char_sorted;
by time1;
run;
data sellerchar_amzn;
set SELLER_CHAR;
where seller_type = 'Amazon New';
rename num_Freeship = Freeship_amzn;
rename num_prime = prime_amzn;
rename max_cumsalefreq = cumsalefreq_amzn;
run;
data sellerchar_usedamzn;
set SELLER_CHAR;
where seller_type = 'Amazon Old';
rename num_Freeship = Freeship_usedamzn;
run;
data sellerchar_used3p;
set SELLER_CHAR;
where seller_type = '3P Old';
rename num_FBA = FBA_used3p;
rename num_prime = prime_used3p;
rename num_Freeship = Freeship_used3p;
run;
proc sort data = sellerchar_amzn (keep = time1 Freeship_amzn prime_amzn cumsalefreq_amzn) out= sellerchar_amzn1;
by time1;
run;

proc sort data = sellerchar_usedamzn (keep = time1 Freeship_usedamzn  ) out= sellerchar_amznused1;
by time1;
run;
proc sort data = sellerchar_used3p (keep = time1 FBA_used3p Freeship_used3p prime_used3p ) out= sellerchar_used3p1;
by time1;
run;
data seller_char_sorted ;
merge seller_char_sorted (in=a) sellerchar_amzn1 (in=b) sellerchar_amznused1 (in =c) sellerchar_used3p1 (in=d);
by time1;
if a ;
if  a and not b then do;
Freeship_amzn = 0;
cumsalefreq_amzn = 0;
prime_amzn = 0;
end;
if a and not c then do;
Freeship_usedamzn = 0;
end;
if a and not d then do;
FBA_used3p = 0;
Freeship_used3p = 0;
prime_used3p = 0;
end;
run;

%macro sellerchar(a,b);
data sellerchar_&b.;
set SELLER_CHAR;
where seller_type = &a.;
rename num_FBA = num_FBA_&b.;
rename num_prime = num_prime_&b.;
rename num_Freeship = num_Freeship_&b.;
rename max_perct_sellerrating = max_perct_sellerrating_&b.;
rename mean_perct_sellerrating = mean_perct_sellerrating_&b.;
rename min_perct_sellerrating = min_perct_sellerrating_&b.;
rename max_num_seller_rating = max_num_seller_rating_&b.;
rename mean_num_seller_rating = mean_num_seller_rating_&b.;
rename min_num_seller_rating = min_num_seller_rating_&b.;
rename max_seller_star_rating = max_seller_star_rating_&b.;
rename mean_seller_star_rating = mean_seller_star_rating_&b.;
rename min_seller_star_rating = min_seller_star_rating_&b.;
rename max_uniq_ASIN = max_uniq_ASIN_&b.;
rename mean_uniq_ASIN = mean_uniq_ASIN_&b.;
rename min_uniq_ASIN = min_uniq_ASIN_&b.;
rename max_cumsalefreq = max_cumsalefreq_&b.;
rename mean_cumsalefreq = mean_cumsalefreq_&b.;
rename min_cumsalefreq = min_cumsalefreq_&b.;
run;

proc sort data = sellerchar_&b. (keep = time1 num_FBA_&b. num_prime_&b. num_Freeship_&b. max_perct_sellerrating_&b. 
mean_perct_sellerrating_&b. min_perct_sellerrating_&b. max_num_seller_rating_&b. mean_num_seller_rating_&b.
min_num_seller_rating_&b. max_seller_star_rating_&b. mean_seller_star_rating_&b. min_seller_star_rating_&b. 
max_uniq_ASIN_&b. mean_uniq_ASIN_&b. min_uniq_ASIN_&b. max_cumsalefreq_&b. mean_cumsalefreq_&b. 
min_cumsalefreq_&b.) out= sellerchar_sorted&b.;
by time1;
run;

data seller_char_sorted ;
merge seller_char_sorted (in=a) sellerchar_sorted&b. (in=b);
by time1;
if a ;
if a and not b then do;
num_FBA_&b. = 0;
num_prime_&b.=0;
num_Freeship_&b. = 0;
max_perct_sellerrating_&b. = 0;
mean_perct_sellerrating_&b. = 0;
min_perct_sellerrating_&b. = 0;
max_num_seller_rating_&b. = 0;
mean_num_seller_rating_&b. = 0;
min_num_seller_rating_&b. = 0;
max_seller_star_rating_&b. = 0;
mean_seller_star_rating_&b. = 0;
min_seller_star_rating_&b. = 0;
max_uniq_ASIN_&b. = 0;
mean_uniq_ASIN_&b. = 0;
min_uniq_ASIN_&b. = 0;
max_cumsalefreq_&b. = 0;
mean_cumsalefreq_&b. = 0;
min_cumsalefreq_&b. = 0;
end;
run;
%mend;
%sellerchar(a='Cluster 1', b = clust1); 
%sellerchar(a='Cluster 2', b = clust2); 


/***********************************/
/*************BRAND ATTRIBUTES*****/
/*********************************/
/**SUMMARIZING PRODUCT CHAR FOR delsy***/

PROC SQL;
create table PRODUCT_CHAR_delsy as
select
time1,
MAX(Answered_Questions) as max_ans_questions_delsy,
mean(Answered_Questions) as mean_ans_questions_delsy,
min(Answered_Questions) as min_ans_questions_delsy,
max(Buy_Box_Price1) as max_buyboxprice_delsy,
mean(Buy_Box_Price1) as mean_buyboxprice_delsy,
min(Buy_Box_Price1) as min_buyboxprice_delsy,
max(nonbuybox3p_price) as max_nonbbox3pprice_delsy,
mean(nonbuybox3p_price) as mean_nonbbox3pprice_delsy,
min(nonbuybox3p_price) as min_nonbbox3pprice_delsy,
max(Product_Star_Rate) as max_product_star_rate_delsy,
mean(Product_Star_Rate) as mean_product_star_rate_delsy,
min(Product_Star_Rate) as min_product_star_rate_delsy,
max(Product_reviews) as max_Product_reviews_delsy,
mean(Product_reviews) as mean_Product_reviews_delsy,
min(Product_reviews) as min_Product_reviews_delsy,
max(Sales_Rank_Own_Categ) as max_salesrank_categ_delsy,
mean(Sales_Rank_Own_Categ) as mean_salesrank_categ_delsy,
min(Sales_Rank_Own_Categ) as min_salesrank_categ_delsy,
max(Sales_Rank_Sub_categ) as max_salesrank_subcateg_delsy,
mean(Sales_Rank_Sub_categ) as mean_salesrank_subcateg_delsy,
min(Sales_Rank_Sub_categ) as min_salesrank_subcateg_delsy
from
focal_rival_prods27
WHERE ASIN IN ('B01ELN68IO','B073ZG6HXV')
group by
time1;
quit;
proc contents data = focal_rival_prods27;
run;
/**MERGING BACK TO THE SELLER CHARACTERISTICS DATA***/
DATA seller_char_sorted;
MERGE seller_char_sorted (IN=A) PRODUCT_CHAR_delsy (IN=B) SELLER_offercharcs_base (in=c);
BY TIME1;
IF A ;
IF A AND NOT B THEN DO;
max_ans_questions_delsy =0;
mean_ans_questions_delsy =0;
min_ans_questions_delsy =0;
max_buyboxprice_delsy =0;
mean_buyboxprice_delsy =0;
min_buyboxprice_delsy =0;
max_nonbbox3pprice_delsy = 0;
mean_nonbbox3pprice_delsy = 0;
min_nonbbox3pprice_delsy = 0;
max_product_star_rate_delsy =0;
mean_product_star_rate_delsy =0;
min_product_star_rate_delsy =0;
max_Product_reviews_delsy =0;
mean_Product_reviews_delsy =0;
min_Product_reviews_delsy =0;
max_salesrank_categ_delsy =0;
mean_salesrank_categ_delsy =0;
min_salesrank_categ_delsy =0;
max_salesrank_subcateg_delsy =0;
mean_salesrank_subcateg_delsy =0;
min_salesrank_subcateg_delsy =0;
END;
RUN;


proc sort data = seller_char_sorted out= ALL_char_sorted ;
by time1;
run;
%macro prodchar(a,b);
data prodchar_&b.;
set focal_rival_prods27;
where ASIN = &a.;
rename Answered_Questions = Answered_Questions&b. ;
rename Buy_Box_Price1 = buyboxprice_&b.;
rename nonbuybox3p_price = nonbbox3pprice_&b.;
rename Product_Star_Rate = product_star_&b.;
rename Product_reviews = Product_reviews_&b.;
rename Sales_Rank_Own_Categ = Sales_Rank_Own_Categ_&b.;
rename Sales_Rank_Sub_categ = Sales_Rank_Sub_categ_&b.;
run;

proc sort data = prodchar_&b. (keep = time1  Answered_Questions&b. buyboxprice_&b. nonbbox3pprice_&b. product_star_&b. 
Product_reviews_&b. Sales_Rank_Own_Categ_&b. Sales_Rank_Sub_categ_&b.) NODUPKEY out= prodchar_sorted&b.;
by time1;
run;

data ALL_char_sorted ;
merge ALL_char_sorted (in=a) prodchar_sorted&b. (in=b);
by time1;
if a;
IF A AND NOT B THEN DO;
Answered_Questions&b. = 0;
buyboxprice_&b. = 0;
nonbbox3pprice_&b. =0;
product_star_&b. = 0;
Product_reviews_&b. = 0;
Sales_Rank_Own_Categ_&b. = 0;
Sales_Rank_Sub_categ_&b. = 0;
END;
run;
%mend;
%prodchar(a= 'B009R1FEQ8', b = smsnite); 
%prodchar(a= 'B001DWK4NM', b = tc);
%prodchar(a= 'B00144MBWQ', b = olympia);
%prodchar(a= 'B004HKSENY', b = rcklnd);
%prodchar(a= 'B01LAS65T0', b = kcole);
/***full list of ASINs and brand**/
/*B00144MBWQ	Olympia*/
/*B001DWK4NM	Travelers Choice*/
/*B004HKSENY	Rockland*/
/*B009R1FEQ8	Samsonite*/
/*B01ELN68IO	Delsey*/
/*B01LAS65T0	Reaction Kenneth Cole*/
/*B073ZG6HXV	Delsey*/

/**MERGING WITH BRAND SELLER PRICE CHANGE TREND**/
DATA ALL_COVARIATES ;
MERGE ALL_char_sorted (IN = A) numpricechange_sellerbrandtrend (IN=B) ;
BY TIME1;
IF A ;
RUN;
/*proc contents data = final_outputvector;*/
/*run;*/
/*****merging with final outcome vector****/

proc sort data = final_outputvector ;
by time1;
run;

data merge_finaldata;
merge final_outputvector (in=a) ALL_COVARIATES (in=b);
by time1;
if a;
rename pricechange_Amzn_olympia = Y_amzn_olympia;
rename pricechange_Amzn_smsnite = Y_amzn_smsnite;
rename pricechange_Amzn_tc = Y_amzn_tc;
rename pricechange_Amzn_rcklnd = Y_amzn_rcklnd;
rename pricechange_clust1_smsnite = Y_clust1_smsnite;
rename pricechange_clust2_tc = Y_clust2_tc;
rename pricechange_clust2_olympia = Y_clust2_olympia;
rename pricechange_clust2_rcklnd = Y_clust2_rcklnd;
run;

proc contents data = merge_finaldata;
run;
proc sort data = merge_finaldata NODUPREC out=merge_finaldata1 ;
by  TIME1 ;
run;


proc expand data=merge_finaldata1 out=merge_finaldata2 method = none; 
convert	Y_amzn_olympia	=	Y_amzn_olympia_lag1	/transformout=(lag 1);
convert	Y_amzn_olympia	=	Y_amzn_olympia_lag2	/transformout=(lag 2);
convert	Y_amzn_olympia	=	Y_amzn_olympia_lag3	/transformout=(lag 3);
convert	Y_amzn_rcklnd	=	Y_amzn_rcklnd_lag1	/transformout=(lag 1);
convert	Y_amzn_rcklnd	=	Y_amzn_rcklnd_lag2	/transformout=(lag 2);
convert	Y_amzn_rcklnd	=	Y_amzn_rcklnd_lag3	/transformout=(lag 3);
convert	Y_amzn_tc	=	Y_amzn_tc_lag1	/transformout=(lag 1);
convert	Y_amzn_tc	=	Y_amzn_tc_lag2	/transformout=(lag 2);
convert	Y_amzn_tc	=	Y_amzn_tc_lag3	/transformout=(lag 3);
convert	Y_amzn_smsnite	=	Y_amzn_smsnite_lag1	/transformout=(lag 1);
convert	Y_amzn_smsnite	=	Y_amzn_smsnite_lag2	/transformout=(lag 2);
convert	Y_amzn_smsnite	=	Y_amzn_smsnite_lag3	/transformout=(lag 3);
convert	Y_clust1_smsnite	=	Y_clust1_smsnite_lag1	/transformout=(lag 1);
convert	Y_clust1_smsnite	=	Y_clust1_smsnite_lag2	/transformout=(lag 2);
convert	Y_clust1_smsnite	=	Y_clust1_smsnite_lag3	/transformout=(lag 3);
convert	Y_clust2_olympia	=	Y_clust2_olympia_lag1	/transformout=(lag 1);
convert	Y_clust2_olympia	=	Y_clust2_olympia_lag2	/transformout=(lag 2);
convert	Y_clust2_olympia	=	Y_clust2_olympia_lag3	/transformout=(lag 3);
convert	Y_clust2_rcklnd	=	Y_clust2_rcklnd_lag1	/transformout=(lag 1);
convert	Y_clust2_rcklnd	=	Y_clust2_rcklnd_lag2	/transformout=(lag 2);
convert	Y_clust2_rcklnd	=	Y_clust2_rcklnd_lag3	/transformout=(lag 3);
convert	Y_clust2_tc	=	Y_clust2_tc_lag1	/transformout=(lag 1);
convert	Y_clust2_tc	=	Y_clust2_tc_lag2	/transformout=(lag 2);
convert	Y_clust2_tc	=	Y_clust2_tc_lag3	/transformout=(lag 3);
convert	Answered_Questionskcole	=	Answered_Questionskcole_lag1	/transformout=(lag 1);
convert	Answered_Questionsolympia	=	Answered_Questionsolympia_lag1	/transformout=(lag 1);
convert	Answered_Questionsrcklnd	=	Answered_Questionsrcklnd_lag1	/transformout=(lag 1);
convert	Answered_Questionssmsnite	=	Answered_Questionssmsnite_lag1	/transformout=(lag 1);
convert	Answered_Questionstc	=	Answered_Questionstc_lag1	/transformout=(lag 1);
convert	FBA_used3p	=	FBA_used3p_lag1	/transformout=(lag 1);
convert	Freeship_amzn	=	Freeship_amzn_lag1	/transformout=(lag 1);
convert	Freeship_used3p	=	Freeship_used3p_lag1	/transformout=(lag 1);
convert	Freeship_usedamzn	=	Freeship_usedamzn_lag1	/transformout=(lag 1);
convert	NUM_SELLERSamzn	=	NUM_SELLERSamzn_lag1	/transformout=(lag 1);
convert	NUM_SELLERSclust1	=	NUM_SELLERSclust1_lag1	/transformout=(lag 1);
convert	NUM_SELLERSclust2	=	NUM_SELLERSclust2_lag1	/transformout=(lag 1);
convert	NUM_SELLERSused3p	=	NUM_SELLERSused3p_lag1	/transformout=(lag 1);
convert	NUM_SELLERSusedamzn	=	NUM_SELLERSusedamzn_lag1	/transformout=(lag 1);
convert	NUM_SKUSamzn	=	NUM_SKUSamzn_lag1	/transformout=(lag 1);
convert	NUM_SKUSclust1	=	NUM_SKUSclust1_lag1	/transformout=(lag 1);
convert	NUM_SKUSclust2	=	NUM_SKUSclust2_lag1	/transformout=(lag 1);
convert	NUM_SKUSused3p	=	NUM_SKUSused3p_lag1	/transformout=(lag 1);
convert	NUM_SKUSusedamzn	=	NUM_SKUSusedamzn_lag1	/transformout=(lag 1);
convert	NUM_delsyamzn	=	NUM_delsyamzn_lag1	/transformout=(lag 1);
convert	NUM_delsyclust1	=	NUM_delsyclust1_lag1	/transformout=(lag 1);
convert	NUM_delsyclust2	=	NUM_delsyclust2_lag1	/transformout=(lag 1);
convert	NUM_delsyused3p	=	NUM_delsyused3p_lag1	/transformout=(lag 1);
convert	NUM_delsyusedamzn	=	NUM_delsyusedamzn_lag1	/transformout=(lag 1);
convert	Product_reviews_kcole	=	Product_reviews_kcole_lag1	/transformout=(lag 1);
convert	Product_reviews_olympia	=	Product_reviews_olympia_lag1	/transformout=(lag 1);
convert	Product_reviews_rcklnd	=	Product_reviews_rcklnd_lag1	/transformout=(lag 1);
convert	Product_reviews_smsnite	=	Product_reviews_smsnite_lag1	/transformout=(lag 1);
convert	Product_reviews_tc	=	Product_reviews_tc_lag1	/transformout=(lag 1);
convert	Sales_Rank_Own_Categ_kcole	=	salerank_kcole_lag1	/transformout=(lag 1);
convert	Sales_Rank_Own_Categ_olympia	=	salerank_olympia_lag1	/transformout=(lag 1);
convert	Sales_Rank_Own_Categ_rcklnd	=	salerank_rcklnd_lag1	/transformout=(lag 1);
convert	Sales_Rank_Own_Categ_smsnite	=	salerank_smsnite_lag1	/transformout=(lag 1);
convert	Sales_Rank_Own_Categ_tc	=	salerank_tc_lag1	/transformout=(lag 1);
convert	Sales_Rank_Sub_categ_kcole	=	salerank_subcat_kcole_lag1	/transformout=(lag 1);
convert	Sales_Rank_Sub_categ_olympia	=	salerank_subcat_olympia_lag1	/transformout=(lag 1);
convert	Sales_Rank_Sub_categ_rcklnd	=	salerank_subcat_rcklnd_lag1	/transformout=(lag 1);
convert	Sales_Rank_Sub_categ_smsnite	=	salerank_subcat_smsnite_lag1	/transformout=(lag 1);
convert	Sales_Rank_Sub_categ_tc	=	salerank_subcat_tc_lag1	/transformout=(lag 1);
convert	buyboxprice_kcole	=	buyboxprice_kcole_lag1	/transformout=(lag 1);
convert	buyboxprice_olympia	=	buyboxprice_olympia_lag1	/transformout=(lag 1);
convert	buyboxprice_rcklnd	=	buyboxprice_rcklnd_lag1	/transformout=(lag 1);
convert	buyboxprice_smsnite	=	buyboxprice_smsnite_lag1	/transformout=(lag 1);
convert	buyboxprice_tc	=	buyboxprice_tc_lag1	/transformout=(lag 1);
convert	cumsalefreq_amzn	=	cumsalefreq_amzn_lag1	/transformout=(lag 1);
convert	max_Product_reviews_delsy	=	maxProduct_reviews_delsy_lag1	/transformout=(lag 1);
convert	max_ans_questions_delsy	=	maxans_questions_delsy_lag1	/transformout=(lag 1);
convert	max_buyboxprice_delsy	=	maxbuyboxprice_delsy_lag1	/transformout=(lag 1);
convert	max_cumsalefreq_clust1	=	maxcumsalefreq_clust1_lag1	/transformout=(lag 1);
convert	max_cumsalefreq_clust2	=	maxcumsalefreq_clust2_lag1	/transformout=(lag 1);
convert	max_num_seller_rating_clust1	=	maxnum_sellerate_clust1_lag1	/transformout=(lag 1);
convert	max_num_seller_rating_clust2	=	maxnum_sellerate_clust2_lag1	/transformout=(lag 1);
convert	max_perct_sellerrating_clust1	=	maxperct_sellerate_clust1_lag1	/transformout=(lag 1);
convert	max_perct_sellerrating_clust2	=	maxperct_sellerate_clust2_lag1	/transformout=(lag 1);
convert	max_product_star_rate_delsy	=	maxprodstar_rate_delsy_lag1	/transformout=(lag 1);
convert	max_salesrank_categ_delsy	=	maxsalerank_delsy_lag1	/transformout=(lag 1);
convert	max_salesrank_subcateg_delsy	=	maxsaleranksubcat_delsy_lag1	/transformout=(lag 1);
convert	max_seller_star_rating_clust1	=	maxseller_star_clust1_lag1	/transformout=(lag 1);
convert	max_seller_star_rating_clust2	=	maxseller_star_clust2_lag1	/transformout=(lag 1);
convert	max_uniq_ASIN_clust1	=	maxuniq_ASIN_clust1_lag1	/transformout=(lag 1);
convert	max_uniq_ASIN_clust2	=	maxuniq_ASIN_clust2_lag1	/transformout=(lag 1);
convert	mean_Product_reviews_delsy	=	meanProduct_reviews_delsy_lag1	/transformout=(lag 1);
convert	mean_ans_questions_delsy	=	meanans_questions_delsy_lag1	/transformout=(lag 1);
convert	mean_buyboxprice_delsy	=	meanbuyboxprice_delsy_lag1	/transformout=(lag 1);
convert	mean_nonbbox3pprice_delsy	=	meannonbbox3pprice_delsy_lag1	/transformout=(lag 1);
convert	mean_cumsalefreq_clust1	=	meancumsalefreq_clust1_lag1	/transformout=(lag 1);
convert	mean_cumsalefreq_clust2	=	meancumsalefreq_clust2_lag1	/transformout=(lag 1);
convert	mean_num_seller_rating_clust1	=	meannum_sellerate_clust1_lag1	/transformout=(lag 1);
convert	mean_num_seller_rating_clust2	=	meannum_sellerate_clust2_lag1	/transformout=(lag 1);
convert	mean_perct_sellerrating_clust1	=	meanperct_sellerate_clust1_lag1	/transformout=(lag 1);
convert	mean_perct_sellerrating_clust2	=	meanperct_sellerate_clust2_lag1	/transformout=(lag 1);
convert	mean_product_star_rate_delsy	=	meanprodstar_rate_delsy_lag1	/transformout=(lag 1);
convert	mean_salesrank_categ_delsy	=	meansalerank_delsy_lag1	/transformout=(lag 1);
convert	mean_salesrank_subcateg_delsy	=	meansaleranksubcat_delsy_lag1	/transformout=(lag 1);
convert	mean_seller_star_rating_clust1	=	meanseller_star_clust1_lag1	/transformout=(lag 1);
convert	mean_seller_star_rating_clust2	=	meanseller_star_clust2_lag1	/transformout=(lag 1);
convert	mean_uniq_ASIN_clust1	=	meanuniq_ASIN_clust1_lag1	/transformout=(lag 1);
convert	mean_uniq_ASIN_clust2	=	meanuniq_ASIN_clust2_lag1	/transformout=(lag 1);
convert	min_Product_reviews_delsy	=	minProduct_reviews_delsy_lag1	/transformout=(lag 1);
convert	min_ans_questions_delsy	=	minans_questions_delsy_lag1	/transformout=(lag 1);
convert	min_buyboxprice_delsy	=	minbuyboxprice_delsy_lag1	/transformout=(lag 1);
convert	min_cumsalefreq_clust1	=	mincumsalefreq_clust1_lag1	/transformout=(lag 1);
convert	min_cumsalefreq_clust2	=	mincumsalefreq_clust2_lag1	/transformout=(lag 1);
convert	min_num_seller_rating_clust1	=	minnum_sellerate_clust1_lag1	/transformout=(lag 1);
convert	min_num_seller_rating_clust2	=	minnum_sellerate_clust2_lag1	/transformout=(lag 1);
convert	min_perct_sellerrating_clust1	=	minperct_sellerate_clust1_lag1	/transformout=(lag 1);
convert	min_perct_sellerrating_clust2	=	minperct_sellerate_clust2_lag1	/transformout=(lag 1);
convert	min_product_star_rate_delsy	=	minprodstar_rate_delsy_lag1	/transformout=(lag 1);
convert	min_salesrank_categ_delsy	=	minsalerank_delsy_lag1	/transformout=(lag 1);
convert	min_salesrank_subcateg_delsy	=	minsaleranksubcat_delsy_lag1	/transformout=(lag 1);
convert	min_seller_star_rating_clust1	=	minseller_star_clust1_lag1	/transformout=(lag 1);
convert	min_seller_star_rating_clust2	=	minseller_star_clust2_lag1	/transformout=(lag 1);
convert	min_uniq_ASIN_clust1	=	minuniq_ASIN_clust1_lag1	/transformout=(lag 1);
convert	min_uniq_ASIN_clust2	=	minuniq_ASIN_clust2_lag1	/transformout=(lag 1);
convert	nonbbox3pprice_kcole	=	nonbbox3pprice_kcole_lag1	/transformout=(lag 1);
convert	nonbbox3pprice_olympia	=	nonbbox3pprice_oly_lag1	/transformout=(lag 1);
convert	nonbbox3pprice_rcklnd	=	nonbbox3pprice_rcklnd_lag1	/transformout=(lag 1);
convert	nonbbox3pprice_smsnite	=	nonbbox3pprice_sam_lag1	/transformout=(lag 1);
convert	nonbbox3pprice_tc	=	nonbbox3pprice_tc_lag1	/transformout=(lag 1);
convert	num_FBA_clust1	=	num_FBA_clust1_lag1	/transformout=(lag 1);
convert	num_FBA_clust2	=	num_FBA_clust2_lag1	/transformout=(lag 1);
convert	num_Freeship_clust1	=	num_Freeship_clust1_lag1	/transformout=(lag 1);
convert	num_Freeship_clust2	=	num_Freeship_clust2_lag1	/transformout=(lag 1);
convert	num_kcoleamzn	=	num_kcoleamzn_lag1	/transformout=(lag 1);
convert	num_kcoleclust1	=	num_kcoleclust1_lag1	/transformout=(lag 1);
convert	num_kcoleclust2	=	num_kcoleclust2_lag1	/transformout=(lag 1);
convert	num_kcoleused3p	=	num_kcoleused3p_lag1	/transformout=(lag 1);
convert	num_kcoleusedamzn	=	num_kcoleusedamzn_lag1	/transformout=(lag 1);
convert	num_olympiaamzn	=	num_olympiaamzn_lag1	/transformout=(lag 1);
convert	num_olympiaclust1	=	num_olympiaclust1_lag1	/transformout=(lag 1);
convert	num_olympiaclust2	=	num_olympiaclust2_lag1	/transformout=(lag 1);
convert	num_olympiaused3p	=	num_olympiaused3p_lag1	/transformout=(lag 1);
convert	num_olympiausedamzn	=	num_olympiausedamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1delsyamzn	=	numpricechngdelsyamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1delsyclust1	=	numpricechngdelsyclust1_lag1	/transformout=(lag 1);
convert	num_pricechange1delsyclust2	=	numpricechngdelsyclust2_lag1	/transformout=(lag 1);
convert	num_pricechange1delsyused3p	=	numpricechngdelsyused3p_lag1	/transformout=(lag 1);
convert	num_pricechange1delsyusedamzn	=	numpricechngdelsyusedamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1kcoleamzn	=	numpricechngkcoleamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1kcoleclust1	=	numpricechngkcoleclust1_lag1	/transformout=(lag 1);
convert	num_pricechange1kcoleclust2	=	numpricechngkcoleclust2_lag1	/transformout=(lag 1);
convert	num_pricechange1kcoleused3p	=	numpricechngkcoleused3p_lag1	/transformout=(lag 1);
convert	num_pricechange1kcoleusedamzn	=	numpricechngkcoleusedamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1olympiaamzn	=	numpricechngolympiaamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1olympiaclust1	=	numpricechngolympiaclust1_lag1	/transformout=(lag 1);
convert	num_pricechange1olympiaclust2	=	numpricechngolympiaclust2_lag1	/transformout=(lag 1);
convert	num_pricechange1olympiaused3p	=	numpricechngolympiaused3p_lag1	/transformout=(lag 1);
convert	num_pricechange1olympiausedamzn	=	numpricechngolympiausedamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1rcklndamzn	=	numpricechngrcklndamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1rcklndclust1	=	numpricechngrcklndclust1_lag1	/transformout=(lag 1);
convert	num_pricechange1rcklndclust2	=	numpricechngrcklndclust2_lag1	/transformout=(lag 1);
convert	num_pricechange1rcklndused3p	=	numpricechngrcklndused3p_lag1	/transformout=(lag 1);
convert	num_pricechange1rcklndusedamzn	=	numpricechngrcklndusedamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1smsniteamzn	=	numpricechngsmsniteamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1smsniteclust1	=	numpricechngsmsniteclust1_lag1	/transformout=(lag 1);
convert	num_pricechange1smsniteclust2	=	numpricechngsmsniteclust2_lag1	/transformout=(lag 1);
convert	num_pricechange1smsniteused3p	=	numpricechngsmsniteused3p_lag1	/transformout=(lag 1);
convert	num_pricechange1smsniteusedamzn	=	numpricechngsmsniteusedamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1tcamzn	=	numpricechngtcamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1tcclust1	=	numpricechngtcclust1_lag1	/transformout=(lag 1);
convert	num_pricechange1tcclust2	=	numpricechngtcclust2_lag1	/transformout=(lag 1);
convert	num_pricechange1tcused3p	=	numpricechngtcused3p_lag1	/transformout=(lag 1);
convert	num_pricechange1tcusedamzn	=	numpricechngtcusedamzn_lag1	/transformout=(lag 1);
convert	num_prime_clust1	=	num_prime_clust1_lag1	/transformout=(lag 1);
convert	num_prime_clust2	=	num_prime_clust2_lag1	/transformout=(lag 1);
convert	num_rcklndamzn	=	num_rcklndamzn_lag1	/transformout=(lag 1);
convert	num_rcklndclust1	=	num_rcklndclust1_lag1	/transformout=(lag 1);
convert	num_rcklndclust2	=	num_rcklndclust2_lag1	/transformout=(lag 1);
convert	num_rcklndused3p	=	num_rcklndused3p_lag1	/transformout=(lag 1);
convert	num_rcklndusedamzn	=	num_rcklndusedamzn_lag1	/transformout=(lag 1);
convert	num_smsniteamzn	=	num_smsniteamzn_lag1	/transformout=(lag 1);
convert	num_smsniteclust1	=	num_smsniteclust1_lag1	/transformout=(lag 1);
convert	num_smsniteclust2	=	num_smsniteclust2_lag1	/transformout=(lag 1);
convert	num_smsniteused3p	=	num_smsniteused3p_lag1	/transformout=(lag 1);
convert	num_smsniteusedamzn	=	num_smsniteusedamzn_lag1	/transformout=(lag 1);
convert	num_tcamzn	=	num_tcamzn_lag1	/transformout=(lag 1);
convert	num_tcclust1	=	num_tcclust1_lag1	/transformout=(lag 1);
convert	num_tcclust2	=	num_tcclust2_lag1	/transformout=(lag 1);
convert	num_tcused3p	=	num_tcused3p_lag1	/transformout=(lag 1);
convert	num_tcusedamzn	=	num_tcusedamzn_lag1	/transformout=(lag 1);
convert	priceAmzn_olympia	=	priceAmzn_oly_lag1	/transformout=(lag 1);
convert	priceAmzn_smsnite	=	priceAmzn_sam_lag1	/transformout=(lag 1);
convert	priceAmzn_rcklnd	=	priceAmzn_rcklnd_lag1	/transformout=(lag 1);
convert	priceAmzn_tc	=	priceAmzn_tc_lag1	/transformout=(lag 1);
convert	priceclust1_smsnite	=	priceclust1_sam_lag1	/transformout=(lag 1);
convert	priceclust2_olympia	=	priceclust2_oly_lag1	/transformout=(lag 1);
convert	priceclust2_rcklnd	=	priceclust2_rcklnd_lag1	/transformout=(lag 1);
convert	priceclust2_tc	=	priceclust2_tc_lag1	/transformout=(lag 1);
convert	pricediff_lowst_Amzn_olympia	=	pricedifflowstAmzn_oly_lag1	/transformout=(lag 1);
convert	pricediff_lowst_Amzn_smsnite	=	pricedifflowstAmzn_sam_lag1	/transformout=(lag 1);
convert	pricediff_lowst_Amzn_rcklnd	=	pricedifflowstAmzn_rcklnd_lag1	/transformout=(lag 1);
convert	pricediff_lowst_Amzn_tc	=	pricedifflowstAmzn_tc_lag1	/transformout=(lag 1);
convert	pricediff_lowst_clust1_smsnite	=	pricedifflowstclust1_sam_lag1	/transformout=(lag 1);
convert	pricediff_lowst_clust2_olympia	=	pricedifflowstclust2_oly_lag1	/transformout=(lag 1);
convert	pricediff_lowst_clust2_rcklnd	=	pricedifflowstclust2_rcklnd_lag1	/transformout=(lag 1);
convert	pricediff_lowst_clust2_tc	=	pricedifflowstclust2_tc_lag1	/transformout=(lag 1);
convert	prime_amzn	=	prime_amzn_lag1	/transformout=(lag 1);
convert	prime_used3p	=	prime_used3p_lag1	/transformout=(lag 1);
convert	product_star_kcole	=	prodstar_kcole_lag1	/transformout=(lag 1);
convert	product_star_olympia	=	prodstar_olympia_lag1	/transformout=(lag 1);
convert	product_star_rcklnd	=	prodstar_rcklnd_lag1	/transformout=(lag 1);
convert	product_star_smsnite	=	prodstar_smsnite_lag1	/transformout=(lag 1);
convert	product_star_tc	=	prodstar_tc_lag1	/transformout=(lag 1);

run;

/**merging back seasonality indicator to the modeling data ***/

proc sort data = amazon_kitchnequip3 (keep = time1 season) nodupkey out= seasonality_data;
by time1;
run;
data mvrf_model1;
merge merge_finaldata2 (in=a) seasonality_data (in=b);
by time1 ;
if a and b;
run;

/***ADDING External site data*****/

/**luggage SKUs scraped from Walmart and Sears only and not HD***/
/**/
/*PROC import */
/*DATAfile ="C:\Amazon Price Dynamics\New Data\Data Scraped\Walmart_merged\walmart_Pricechangesallcateg_4-6-20.csv" */
/*DBMS=csv*/
/*OUT= wm_pricechange_allcateg*/
/*REPLACE;*/
/*run;*/
/**/
/*PROC import */
/*DATAfile ="C:\Amazon Price Dynamics\New Data\Data Scraped\Sears_merged\Sears_Pricechangesallcateg_4-6-20.csv" */
/*DBMS=csv*/
/*OUT= sears_pricechange_allcateg*/
/*REPLACE;*/
/*run;*/


PROC import 
DATAfile ="D:\Amazon Price Dynamics\New Data\Data Scraped\Walmart_merged\walmart_Pricechangesallcateg_4-6-20.csv" 
DBMS=csv
OUT= wm_pricechange_allcateg
REPLACE;
run;

PROC import 
DATAfile ="D:\Amazon Price Dynamics\New Data\Data Scraped\Sears_merged\Sears_Pricechangesallcateg_4-6-20.csv" 
DBMS=csv
OUT= sears_pricechange_allcateg
REPLACE;
run;

data sears_pricechange_allcateg;
set sears_pricechange_allcateg;
rename date = time1;
run;

proc sql;
create table WM as
select 
time1,
categ,
max(pricechange) as max_pricechange_wm,
mean(pricechange) as mean_pricechange_wm,
min(pricechange) as min_pricechange_wm
from
wm_pricechange_allcateg
where time1 >= '14Dec2017'd and time1 <= '06May2018'd 
group by
time1,
categ;
quit;
data wm1;
set wm;
if abs(min_pricechange_wm) > abs(max_pricechange_wm) then maxpricechange_wm = min_pricechange_wm;
else if abs(min_pricechange_wm) < abs(max_pricechange_wm) then maxpricechange_wm = max_pricechange_wm;
else maxpricechange_wm = 0;
run;
proc freq data = wm1;
tables time1;run;

proc sql;
create table Sears as
select 
time1,
categ,
max(pricechange) as max_pricechange_sears,
mean(pricechange) as mean_pricechange_sears,
min(pricechange) as min_pricechange_sears
from
sears_pricechange_allcateg
where time1 >= '14Dec2017'd and time1 <= '06May2018'd 
group by
time1,
categ;
quit;
data sears1;
set sears;
if abs(min_pricechange_sears) > abs(max_pricechange_sears) then maxpricechange_sears = min_pricechange_sears;
else if abs(min_pricechange_sears) < abs(max_pricechange_sears) then maxpricechange_sears = max_pricechange_sears;
else maxpricechange_sears = 0;
run;

/*data merged_allwebsites (drop=  min_pricechange_wm max_pricechange_wm*/
/*min_pricechange_sears max_pricechange_sears);*/
/*merge WM1 (in=b) Sears1 (in=c);*/
/*by time1 categ;*/
/*if  b and c;*/
/*if abs(maxpricechange_WM) = max(abs(maxpricechange_WM),abs(maxpricechange_sears)) then maxpricechange_externalsite = maxpricechange_WM;*/
/*else if abs(maxpricechange_sears) = max(abs(maxpricechange_WM),abs(maxpricechange_sears)) then maxpricechange_externalsite = maxpricechange_sears;*/
/*else maxpricechange_externalsite = 0;*/
/*meanpricechange_externalsite = mean(mean_pricechange_WM,mean_pricechange_sears);*/
/*run;*/

data merged_allwebsites (drop= min_pricechange_wm max_pricechange_wm
min_pricechange_sears max_pricechange_sears);
merge WM1 (in=b) Sears1 (in=c);
by time1 categ;
if b and c;
run;


/**export this above file for all other categories**/
/**extracting only Luggage categ***/



data external_luggageprices;
set merged_allwebsites;
where categ = 'Luggage';
run;
proc freq data = merged_allwebsites;
tables categ;
run;

/**/
/*proc sort data = external_luggageprices (drop = maxpricechange_WM mean_pricechange_WM */
/*maxpricechange_sears mean_pricechange_sears categ)*/
/*out=externalsite_data2;*/
/*by time1;*/
/*run;*/
/**/
/*proc expand data=externalsite_data2 out=externalsite_data3 method = none; */
/*convert	maxpricechange_externalsite	=	maxpricechange_extsite_lag1	/transformout=(lag 1);*/
/*convert	meanpricechange_externalsite	=	meanpricechange_extsite_lag1	/transformout=(lag 1);*/
/*run;*/
/*data externalsite_data4 (drop= maxpricechange_externalsite meanpricechange_externalsite);*/
/*set externalsite_data3;*/
/*run;*/
/**/
/*proc sort data = mvrf_model1;*/
/*by time1;*/
/*run;*/
/**/
/*data mvrfdata_withexternalsite;*/
/*merge mvrf_model1 (in=a) externalsite_data4 (in=b);*/
/*by time1;*/
/*if a;*/
/*if a and not b then do;*/
/*externalsite_ind = 0;*/
/*maxpricechange_extsite_lag1 = 0;*/
/*meanpricechange_extsite_lag1 = 0;*/
/*end;*/
/*else externalsite_ind = 1;*/
/*run;*/
/*proc contents data = mvrfdata_withexternalsite;*/
/*run;*/

/*PROC EXPORT */
/*DATA=mvrfdata_withexternalsite*/
/*DBMS=csv*/
/*OUTFILE= "C:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\mvrfdata_withexternalsite_luggage_May2020.csv"*/
/*REPLACE;*/
/*run;*/


/***importing data for work post JM RnR***/

/*PROC import */
/*DATAFILE ="D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\mvrfdata_withexternalsite_luggage_May2020.csv"*/
/*DBMS=csv*/
/*OUT= mvrfdata_withexternalsite*/
/*REPLACE;*/
/*run;*/
/***merging with edited external site data****/
proc sort data = mvrf_model1;
by time1;
run;

proc sort data = external_luggageprices (drop = categ) out = externalsite_data2;
by time1;
run;
data mvrfdata_withexternalsite1;
merge mvrf_model1 (in=a) externalsite_data2 (in=b);
by time1;
if a;

if a and not b then do;
ext_site = 0;
maxpricechange_sears = 0;     
maxpricechange_wm = 0;     
mean_pricechange_sears = 0;     
mean_pricechange_wm = 0;
end;
else ext_site =1;
run;
/***lagging external site price changes****/ 
data mvrfdata_withexternalsite2;
set mvrfdata_withexternalsite1;
maxpricechange_searslag1 = lag(maxpricechange_sears);   
maxpricechange_wmlag1 = lag(maxpricechange_wm);
maxpricechange_searslag2 = lag2(maxpricechange_sears);   
maxpricechange_wmlag2 = lag2(maxpricechange_wm);
maxpricechange_searslag3 = lag3(maxpricechange_sears);   
maxpricechange_wmlag3 = lag3(maxpricechange_wm);
run;
/*proc freq data = mvrfdata_withexternalsite2;*/
/*tables ext_site;*/
/*run;*/


/***DAY OF THE WEEK IDENTIFICATION**/

data mvrfdata_withexternalsite3;
set mvrfdata_withexternalsite2;
day_week = weekday(time1);
if day_week = 1 then sunday = 1; else sunday = 0;
if day_week = 2 then monday = 1; else monday = 0;
if day_week = 3 then tuesday = 1; else tuesday = 0;
if day_week = 4 then wednesday = 1; else wednesday = 0;
if day_week = 5 then thursday = 1; else thursday = 0;
if day_week = 6 then friday = 1; else friday = 0;
if day_week = 7 then saturday = 1; else saturday = 0;
if day_week in (1,7) then weekend = 1; else weekend = 0;
run;



/**SALES RANK CATEGORIZATION, TOP AND BOTTOM BRANDS IN PRE PERIOD**/
DATA mvrfdata_withexternalsite4;
SET mvrfdata_withexternalsite3;
meansalerank1_delsy_lag1 = input(meansalerank_delsy_lag1,8.);
minsalerank1_delsy_lag1 = input(minsalerank_delsy_lag1,8.);
maxsalerank1_delsy_lag1 = input(maxsalerank_delsy_lag1,8.);
salerank1_kcole_lag1 = input(salerank_kcole_lag1,8.);
salerank1_olympia_lag1 = input(salerank_olympia_lag1,8.);
salerank1_rcklnd_lag1 = input(salerank_rcklnd_lag1,8.);
salerank1_smsnite_lag1 = input(salerank_smsnite_lag1,8.);
salerank1_tc_lag1 = input(salerank_tc_lag1,8.);
run;

proc contents data = mvrfdata_withexternalsite2;
run;

/*PROC UNIVARIATE DATA = mvrfdata_withexternalsite2 ;*/
/*VAR */
/*minsalerank1_delsy_lag1*/
/*salerank1_kcole_lag1*/
/*salerank1_olympia_lag1*/
/*salerank1_rcklnd_lag1*/
/*salerank1_smsnite_lag1*/
/*salerank1_tc_lag1*/
/*;*/
/*RUN;*/

data mvrfdata_withexternalsite5;
set mvrfdata_withexternalsite4;
top_salesrank = min(ifn(meansalerank_delsy_lag1=0,.,meansalerank_delsy_lag1),
                    ifn(minsalerank_delsy_lag1=0,.,minsalerank_delsy_lag1),
                    ifn(maxsalerank_delsy_lag1=0,.,maxsalerank_delsy_lag1), 
                    ifn(salerank1_olympia_lag1=0,.,salerank1_olympia_lag1),
                    ifn(salerank1_rcklnd_lag1=0,.,salerank1_rcklnd_lag1),
                    ifn(salerank1_smsnite_lag1=0,.,salerank1_smsnite_lag1),
					ifn(salerank1_tc_lag1=0,.,salerank1_tc_lag1));

bottom_salesrank = max(ifn(meansalerank_delsy_lag1=0,.,meansalerank_delsy_lag1),
                    ifn(minsalerank_delsy_lag1=0,.,minsalerank_delsy_lag1),
                    ifn(maxsalerank_delsy_lag1=0,.,maxsalerank_delsy_lag1), 
                    ifn(salerank1_olympia_lag1=0,.,salerank1_olympia_lag1),
                    ifn(salerank1_rcklnd_lag1=0,.,salerank1_rcklnd_lag1),
                    ifn(salerank1_smsnite_lag1=0,.,salerank1_smsnite_lag1),
					ifn(salerank1_tc_lag1=0,.,salerank1_tc_lag1));
run;



data mvrfdata_withexternalsite6;
set mvrfdata_withexternalsite5;

if top_salesrank ne . and top_salesrank ne bottom_salesrank then do;
if minsalerank_delsy_lag1 = top_salesrank then delsy_topbrandlag1 = 1; else delsy_topbrandlag1 = 0; 
if salerank1_olympia_lag1 = top_salesrank then oly_topbrandlag1 = 1; else oly_topbrandlag1 = 0; 
if salerank1_rcklnd_lag1 = top_salesrank then rcklnd_topbrandlag1 = 1; else rcklnd_topbrandlag1 = 0; 
if salerank1_smsnite_lag1 = top_salesrank then smsnte_topbrandlag1 = 1; else smsnte_topbrandlag1 = 0; 
if salerank1_tc_lag1 = top_salesrank then tc_topbrandlag1 = 1; else tc_topbrandlag1 = 0;
end;

if bottom_salesrank ne . and top_salesrank ne bottom_salesrank then do;
if minsalerank_delsy_lag1 = bottom_salesrank then delsy_bottombrandlag1 = 1; else delsy_bottombrandlag1 = 0; 
if salerank1_olympia_lag1 = bottom_salesrank then oly_bottombrandlag1 = 1; else oly_bottombrandlag1 = 0; 
if salerank1_rcklnd_lag1 = bottom_salesrank then rcklnd_bottombrandlag1 = 1; else rcklnd_bottombrandlag1 = 0; 
if salerank1_smsnite_lag1 = bottom_salesrank then smsnte_bottombrandlag1 = 1; else smsnte_bottombrandlag1 = 0; 
if salerank1_tc_lag1 = bottom_salesrank then tc_bottombrandlag1 = 1; else tc_bottombrandlag1 = 0;
end;

else do;
delsy_topbrandlag1 = 0;
oly_topbrandlag1 = 0; 
rcklnd_topbrandlag1 = 0; 
smsnte_topbrandlag1 = 0; 
tc_topbrandlag1 = 0;
delsy_bottombrandlag1 = 0;
oly_bottombrandlag1 = 0; 
rcklnd_bottombrandlag1 = 0; 
smsnte_bottombrandlag1 = 0; 
tc_bottombrandlag1 = 0;
end;

run;
proc contents data = mvrfdata_withexternalsite6;
run;


/***creating main effect of price change**/
data final_gam_modeldata;
set mvrfdata_withexternalsite6;

/*price increase/decrease of 5% and -5%, with direction**/
/**Olympia*/
if Y_amzn_olympia_lag1 >= 0.05 and Y_amzn_olympia_lag1 < 0.1 then incr5per_amzn_oly = 1; else incr5per_amzn_oly = 0;
if Y_amzn_olympia_lag1 <= -0.05 and Y_amzn_olympia_lag1 > -0.1 then decr5per_amzn_oly = 1; else decr5per_amzn_oly = 0;

if Y_amzn_olympia_lag1 >= 0.1 and Y_amzn_olympia_lag1 < 0.2 then incr10per_amzn_oly = 1; else incr10per_amzn_oly = 0;
if Y_amzn_olympia_lag1 <= -0.1 and Y_amzn_olympia_lag1 > -0.2 then decr10per_amzn_oly = 1; else decr10per_amzn_oly = 0;

if Y_amzn_olympia_lag1 >= 0.2 then incr20per_amzn_oly = 1; else incr20per_amzn_oly = 0;
if Y_amzn_olympia_lag1 <= -0.2 then decr20per_amzn_oly = 1; else decr20per_amzn_oly = 0;

/**rcklnd*/
if Y_amzn_rcklnd_lag1 >= 0.05 and Y_amzn_rcklnd_lag1 < 0.1 then incr5per_amzn_rcklnd = 1; else incr5per_amzn_rcklnd = 0;
if Y_amzn_rcklnd_lag1 <= -0.05 and Y_amzn_rcklnd_lag1 > -0.1 then decr5per_amzn_rcklnd = 1; else decr5per_amzn_rcklnd = 0;

if Y_amzn_rcklnd_lag1 >= 0.1 and Y_amzn_rcklnd_lag1 < 0.2 then incr10per_amzn_rcklnd = 1; else incr10per_amzn_rcklnd = 0;
if Y_amzn_rcklnd_lag1 <= -0.1 and Y_amzn_rcklnd_lag1 > -0.2 then decr10per_amzn_rcklnd = 1; else decr10per_amzn_rcklnd = 0;

if Y_amzn_rcklnd_lag1 >= 0.2 then incr20per_amzn_rcklnd = 1; else incr20per_amzn_rcklnd = 0;
if Y_amzn_rcklnd_lag1 <= -0.2 then decr20per_amzn_rcklnd = 1; else decr20per_amzn_rcklnd = 0;

/**tc*/
if Y_amzn_tc_lag1 >= 0.05 and Y_amzn_tc_lag1 < 0.1 then incr5per_amzn_tc = 1; else incr5per_amzn_tc = 0;
if Y_amzn_tc_lag1 <= -0.05 and Y_amzn_tc_lag1 > -0.1 then decr5per_amzn_tc = 1; else decr5per_amzn_tc = 0;

if Y_amzn_tc_lag1 >= 0.1 and Y_amzn_tc_lag1 < 0.2 then incr10per_amzn_tc = 1; else incr10per_amzn_tc = 0;
if Y_amzn_tc_lag1 <= -0.1 and Y_amzn_tc_lag1 > -0.2 then decr10per_amzn_tc = 1; else decr10per_amzn_tc = 0;

if Y_amzn_tc_lag1 >= 0.2 then incr20per_amzn_tc = 1; else incr20per_amzn_tc = 0;
if Y_amzn_tc_lag1 <= -0.2 then decr20per_amzn_tc = 1; else decr20per_amzn_tc = 0;

/*Samsonite*/
if Y_amzn_smsnite_lag1 >= 0.05 and Y_amzn_smsnite_lag1 < 0.1 then incr5per_amzn_sam = 1; else incr5per_amzn_sam = 0;
if Y_amzn_smsnite_lag1 <= -0.05 and Y_amzn_smsnite_lag1 > -0.1 then decr5per_amzn_sam = 1; else decr5per_amzn_sam = 0;

if Y_amzn_smsnite_lag1 >= 0.1 and Y_amzn_smsnite_lag1 < 0.2 then incr10per_amzn_sam = 1; else incr10per_amzn_sam = 0;
if Y_amzn_smsnite_lag1 <= -0.1 and Y_amzn_smsnite_lag1 > -0.2 then decr10per_amzn_sam = 1; else decr10per_amzn_sam = 0;

if Y_amzn_smsnite_lag1 >= 0.2 then incr20per_amzn_sam = 1; else incr20per_amzn_sam = 0;
if Y_amzn_smsnite_lag1 <= -0.2 then decr20per_amzn_sam = 1; else decr20per_amzn_sam = 0;

if Y_clust1_smsnite_lag1 >= 0.05 and Y_clust1_smsnite_lag1 < 0.1 then incr5per_3p_sam = 1; else incr5per_3p_sam = 0;
if Y_clust1_smsnite_lag1 <= -0.05 and Y_clust1_smsnite_lag1 > -0.1 then decr5per_3p_sam = 1; else decr5per_3p_sam = 0;

if Y_clust1_smsnite_lag1 >= 0.1 and Y_clust1_smsnite_lag1 < 0.2 then incr10per_3p_sam = 1; else incr10per_3p_sam = 0;
if Y_clust1_smsnite_lag1 <= -0.1 and Y_clust1_smsnite_lag1 > -0.2 then decr10per_3p_sam = 1; else decr10per_3p_sam = 0;

if Y_clust1_smsnite_lag1 >= 0.2 then incr20per_3p_sam = 1; else incr20per_3p_sam = 0;
if Y_clust1_smsnite_lag1 <= -0.2 then decr20per_3p_sam = 1; else decr20per_3p_sam = 0;

/*Olympia*/
if Y_clust2_olympia_lag1 >= 0.05 and Y_clust2_olympia_lag1 < 0.1 then incr5per_3p_oly = 1; else incr5per_3p_oly = 0;
if Y_clust2_olympia_lag1 <= -0.05 and Y_clust2_olympia_lag1 > -0.1 then decr5per_3p_oly = 1; else decr5per_3p_oly = 0;

if Y_clust2_olympia_lag1 >= 0.1 and Y_clust2_olympia_lag1 < 0.2 then incr10per_3p_oly = 1; else incr10per_3p_oly = 0;
if Y_clust2_olympia_lag1 <= -0.1 and Y_clust2_olympia_lag1 > -0.2 then decr10per_3p_oly = 1; else decr10per_3p_oly = 0;

if Y_clust2_olympia_lag1 >= 0.2 then incr20per_3p_oly = 1; else incr20per_3p_oly = 0;
if Y_clust2_olympia_lag1 <= -0.2 then decr20per_3p_oly = 1; else decr20per_3p_oly = 0;

/*Rockland*/
if Y_clust2_rcklnd_lag1 >= 0.05 and Y_clust2_rcklnd_lag1 < 0.1 then incr5per_3p_rcklnd = 1; else incr5per_3p_rcklnd = 0;
if Y_clust2_rcklnd_lag1 <= -0.05 and Y_clust2_rcklnd_lag1 > -0.1 then decr5per_3p_rcklnd = 1; else decr5per_3p_rcklnd = 0;

if Y_clust2_rcklnd_lag1 >= 0.1 and Y_clust2_rcklnd_lag1 < 0.2 then incr10per_3p_rcklnd = 1; else incr10per_3p_rcklnd = 0;
if Y_clust2_rcklnd_lag1 <= -0.1 and Y_clust2_rcklnd_lag1 > -0.2 then decr10per_3p_rcklnd = 1; else decr10per_3p_rcklnd = 0;

if Y_clust2_rcklnd_lag1 >= 0.2 then incr20per_3p_rcklnd = 1; else incr20per_3p_rcklnd = 0;
if Y_clust2_rcklnd_lag1 <= -0.2 then decr20per_3p_rcklnd = 1; else decr20per_3p_rcklnd = 0;

/*Traveler's Choice*/
if Y_clust2_tc_lag1 >= 0.05 and Y_clust2_tc_lag1 < 0.1 then incr5per_3p_tc = 1; else incr5per_3p_tc = 0;
if Y_clust2_tc_lag1 <= -0.05 and Y_clust2_tc_lag1 > -0.1 then decr5per_3p_tc = 1; else decr5per_3p_tc = 0;

if Y_clust2_tc_lag1 >= 0.1 and Y_clust2_tc_lag1 < 0.2 then incr10per_3p_tc = 1; else incr10per_3p_tc = 0;
if Y_clust2_tc_lag1 <= -0.1 and Y_clust2_tc_lag1 > -0.2 then decr10per_3p_tc = 1; else decr10per_3p_tc = 0;

if Y_clust2_tc_lag1 >= 0.2 then incr20per_3p_tc = 1; else incr20per_3p_tc = 0;
if Y_clust2_tc_lag1 <= -0.2 then decr20per_3p_tc = 1; else decr20per_3p_tc = 0;

run;
proc contents data = final_gam_modeldata;
run;

PROC EXPORT 
DATA=final_gam_modeldata
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\GAM + MVRF\Other categories\Luggage\final_gam_modeldata_JMRnd4_July2025.csv"
REPLACE;
run;
libname inn 'D:\Amazon Price Dynamics\New Data\GAM + MVRF\Other categories\Luggage';
data inn.final_gam_modeldata_July2025;
set final_gam_modeldata;
run;



/**/
/*proc sql;*/
/*create table summ_num_changes as*/
/*select*/
/*sum(case when Y_amzn_delsy_lag1 ne 0 then 1 else 0 end) as num_amzn_delsychanges,*/
/*sum(case when Y_amzn_smsnite_lag1 ne 0 then 1 else 0 end) as num_amzn_samchanges,*/
/*sum(case when Y_clust1_smsnite_lag1 ne 0 then 1 else 0 end) as num_3p_samchanges,*/
/*sum(case when Y_clust2_olympia_lag1 ne 0 then 1 else 0 end) as num_3p_olychanges,*/
/*sum(case when Y_clust2_rcklnd_lag1 ne 0 then 1 else 0 end) as num_3p_rcklndchanges,*/
/*sum(case when Y_clust2_tc_lag1 ne 0 then 1 else 0 end) as num_3p_tcchanges,*/
/**/
/*sum(case when incr5per_amzn_delsy ne 0 then 1 else 0 end) as num_incr5peramzn_delsychanges,*/
/*sum(case when decr5per_amzn_delsy ne 0 then 1 else 0 end) as num_decr5peramzn_delsychanges,*/
/*sum(case when incr10per_amzn_delsy ne 0 then 1 else 0 end) as num_incr10peramzn_delsychanges,*/
/*sum(case when decr10per_amzn_delsy ne 0 then 1 else 0 end) as num_decr10peramzn_delsychanges,*/
/*sum(case when incr20per_amzn_delsy ne 0 then 1 else 0 end) as num_incr20peramzn_delsychanges,*/
/*sum(case when decr20per_amzn_delsy ne 0 then 1 else 0 end) as num_decr20peramzn_delsychanges,*/
/**/
/*sum(case when incr5per_amzn_sam ne 0 then 1 else 0 end) as num_incr5peramzn_samchanges,*/
/*sum(case when decr5per_amzn_sam ne 0 then 1 else 0 end) as num_decr5peramzn_samchanges,*/
/*sum(case when incr10per_amzn_sam ne 0 then 1 else 0 end) as num_incr10peramzn_samchanges,*/
/*sum(case when decr10per_amzn_sam ne 0 then 1 else 0 end) as num_decr10peramzn_samchanges,*/
/*sum(case when incr20per_amzn_sam ne 0 then 1 else 0 end) as num_incr20peramzn_samchanges,*/
/*sum(case when decr20per_amzn_sam ne 0 then 1 else 0 end) as num_decr20peramzn_samchanges,*/
/**/
/*sum(case when incr5per_3p_sam ne 0 then 1 else 0 end) as num_incr5per3p_samchanges,*/
/*sum(case when decr5per_3p_sam ne 0 then 1 else 0 end) as num_decr5per3p_samchanges,*/
/*sum(case when incr10per_3p_sam ne 0 then 1 else 0 end) as num_incr10per3p_samchanges,*/
/*sum(case when decr10per_3p_sam ne 0 then 1 else 0 end) as num_decr10per3p_samchanges,*/
/*sum(case when incr20per_3p_sam ne 0 then 1 else 0 end) as num_incr20per3p_samchanges,*/
/*sum(case when decr20per_3p_sam ne 0 then 1 else 0 end) as num_decr20per3p_samchanges,*/
/**/
/*sum(case when incr5per_3p_oly ne 0 then 1 else 0 end) as num_incr5per3p_olychanges,*/
/*sum(case when decr5per_3p_oly ne 0 then 1 else 0 end) as num_decr5per3p_olychanges,*/
/*sum(case when incr10per_3p_oly ne 0 then 1 else 0 end) as num_incr10per3p_olychanges,*/
/*sum(case when decr10per_3p_oly ne 0 then 1 else 0 end) as num_decr10per3p_olychanges,*/
/*sum(case when incr20per_3p_oly ne 0 then 1 else 0 end) as num_incr20per3p_olychanges,*/
/*sum(case when decr20per_3p_oly ne 0 then 1 else 0 end) as num_decr20per3p_olychanges,*/
/**/
/**/
/*sum(case when incr5per_3p_rcklnd ne 0 then 1 else 0 end) as num_incr5per3p_rcklndchanges,*/
/*sum(case when decr5per_3p_rcklnd ne 0 then 1 else 0 end) as num_decr5per3p_rcklndchanges,*/
/*sum(case when incr10per_3p_rcklnd ne 0 then 1 else 0 end) as num_incr10per3p_rcklndchanges,*/
/*sum(case when decr10per_3p_rcklnd ne 0 then 1 else 0 end) as num_decr10per3p_rcklndchanges,*/
/*sum(case when incr20per_3p_rcklnd ne 0 then 1 else 0 end) as num_incr20per3p_rcklndchanges,*/
/*sum(case when decr20per_3p_rcklnd ne 0 then 1 else 0 end) as num_decr20per3p_rcklndchanges,*/
/**/
/*sum(case when incr5per_3p_tc ne 0 then 1 else 0 end) as num_incr5per3p_tcchanges,*/
/*sum(case when decr5per_3p_tc ne 0 then 1 else 0 end) as num_decr5per3p_tcchanges,*/
/*sum(case when incr10per_3p_tc ne 0 then 1 else 0 end) as num_incr10per3p_tcchanges,*/
/*sum(case when decr10per_3p_tc ne 0 then 1 else 0 end) as num_decr10per3p_tcchanges,*/
/*sum(case when incr20per_3p_tc ne 0 then 1 else 0 end) as num_incr20per3p_tcchanges,*/
/*sum(case when decr20per_3p_tc ne 0 then 1 else 0 end) as num_decr20per3p_tcchanges*/
/**/
/*from */
/*final_gam_modeldata;*/
/*quit;*/

proc print data = summ_num_changes;
run;
